//
//  FMDBManager.m
//  FMDBDemo
//
//  Created by YY on 16/10/16.
//  Copyright © 2016年 CheDan. All rights reserved.
//


/*
 一、外连接
 外连接可分为：左连接、右连接、完全外连接(full outer join)。
 二、内连接  join 或 inner join
 */


#import "FMDBManager.h"
#import <FMDB/FMDB.h>
#import <UIKit/UIKit.h>

#define DB_NAME @"user.db"

@interface FMDBManager ()

@property (nonatomic,copy)FMDatabase *fmDataBase;

@end

static FMDBManager *manager;

@implementation FMDBManager

+ (instancetype)shareManager{

    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[self alloc]init];
    });
    return manager;
}
- (NSArray *)executeNeiLianSql{
    /*
        内联(inner join)。
        select * from T1 ,T3 where T1.userid = T3.userid(其实这样的结果等同于select * from T1 inner join T3 on T1.userid=T3.userid )。
     　　把两个表中都存在userid的行拼成一行(即内联)，但后者的效率会比前者高很多，建议用后者(内联)的写法。
     　　SQL语句：select * from T1 inner join T2 on T1.userid=T2.userid
     
     

        左联 left outer join
        SQL语句：select * from T1 left outer join T2 on T1.userid=T2.userid
     
        右联  right outer join
        SQL语句：select * from T1 left outer join T2 on T1.userid=T2.userid
     
     
        全联  full outer join
        SQL语句：select * from T1 full outer join T2 on T1.userid=T2.userid
     
     */
    NSMutableArray *data = [NSMutableArray array];
    NSString *sql = @"SELECT * from Member inner join Score on Member.MID = Score.MID";
    FMResultSet *set = [self.fmDataBase executeQuery:sql ];
    while ([set next]) {
        Member * member = [Member new];
        member.MID = [set stringForColumn:@"MID"];
        member.MName = [set stringForColumn:@"MName"];
        [data addObject:member];
    }
    if (data.count > 0) {
        return [data copy];
    }
    return nil;
    
}

- (void)inserMember:(Member *)model{

    Member *core = model;
    NSString *insertSQL = @"insert into Member (MID, MName) values (?, ?)";
    if (![self.fmDataBase executeUpdate:insertSQL, core.MID, core.MName]) {
        NSLog(@"插入失败 : %@",[self.fmDataBase lastErrorMessage]);
    }
}

- (void)inserF:(F *)model{
    F *core = model;
    NSString *insertSQL = @"insert into F (FID, FName) values (?, ?)";
    if (![self.fmDataBase executeUpdate:insertSQL, core.FID, core.FName]) {
        NSLog(@"插入失败 : %@",[self.fmDataBase lastErrorMessage]);
    }
}

- (void)inserScore:(Score *)model{
    Score *core = model;
    NSString *insertSQL = @"insert into Score (SID, FID, MID, Score) values (?, ?, ?, ?)";
    if (![self.fmDataBase executeUpdate:insertSQL, core.SID, core.FID,core.MID, core.Score]) {
        NSLog(@"插入失败 : %@",[self.fmDataBase lastErrorMessage]);
    }
}


- (void)createTable{
    NSArray *tables =  @[@"create table if not exists Member(serial integer Primary Key Autoincrement, MID varchar(64), MName varchar (64))",
                         @"create table if not exists F(serial integer Primary Key Autoincrement, FID varchar(64), FName varchar (64))",
                         @"create table if not exists Score(serial integer Primary Key Autoincrement, SID varchar(64), FID varchar (64),MID varchar (64), Score varchar (64))"];
    
    for (NSString * createSQL in tables) {
        if (![self.fmDataBase executeUpdate:createSQL]) {
            NSLog(@"创建表失败");
        }
    }
}

- (FMDatabase *)fmDataBase{
    if (!_fmDataBase) {
        _fmDataBase = [[FMDatabase alloc]initWithPath:[self dbPath]];
        NSLog(@"dbPath %@",[self dbPath]);
        if ([_fmDataBase open]) {
            [self createTable];
        }else{
            NSLog(@"_fmDataBase 初始化失败");
        }
    }
    return _fmDataBase;
}
- (NSString *)dbPath{
     NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    return [doc stringByAppendingPathComponent:DB_NAME];
}
/*
 
 @"SELECT * from Member inner join Score on Member.MID = Score.MID"
 
 tableName -> Member
 state -> SQLStateInnerJoin
 queryString -> Member.MID = Score.MID
 
 */
- (NSArray *)querySQLStringWithTableName:(NSString *)tableName
                                  state:(SQLState)state
                         querySQLString:(NSString *)queryString{
    NSMutableArray *data = [NSMutableArray array];
    NSString *sql = [self findSQLStringWithTableName:tableName state:state querySQLString:queryString];
    FMResultSet *set = [self.fmDataBase executeQuery:sql ];
    while ([set next]) {
        Member * member = [Member new];
        member.MID = [set stringForColumn:@"MID"];
        member.MName = [set stringForColumn:@"MName"];
        [data addObject:member];
    }
    if (data.count > 0) {
        return [data copy];
    }
    return nil;

}



/*
    @"SELECT * from Member inner join Score on Member.MID = Score.MID"
 
    tableName -> Member
    state -> SQLStateInnerJoin
    queryString -> Member.MID = Score.MID
 
 */
- (NSString *)findSQLStringWithTableName:(NSString *)tableName
                                   state:(SQLState)state
                          querySQLString:(NSString *)queryString{
    self.sqlState = state;
    return [NSString stringWithFormat:@"SELECT * from %@ %@ %@",tableName,[self stringWithSQLState:state],queryString];
}
- (NSString *)findSQLStringWithTableName:(NSString *)tableName
                          querySQLString:(NSString *)queryString{
    return [self findSQLStringWithTableName:tableName state:self.sqlState querySQLString:queryString];
}


- (NSString *)stringWithSQLState:(SQLState)state{
    NSString *str = @"";
    /*
     
     SQLStateInnerJoin,        //内联  inner join
     SQLStateLeftOuterJoin ,   //左联  left outer join
     SQLStateRightOuterJoin,   //右联  right outer join
     SQLStateFullOuterJoin     //全联  full outer join
     
     */
    switch (state) {
        case SQLStateInnerJoin:
            str = @"inner join on";
            break;
        case SQLStateLeftOuterJoin:
            str = @"left outer join on";
            break;
        case SQLStateRightOuterJoin:
            str = @"right outer join on";
            break;
        case SQLStateFullOuterJoin:
            str = @"full outer join on";
            break;
            default:break;
    }
    return str;
}

@end
